﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Daycare.Models;

namespace Daycare.DAL
{
    public class DocumentRepository
    {
        public List<DocumentType> GetDocumentTypes()
        {
            DataTable tempTable = new DataTable();
            List<DocumentType> documentTypes = new List<DocumentType>();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand("daycare.GetDocumentTypes", con);
                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return documentTypes;
            }

            foreach (DataRow row in tempTable.Rows)
            {
                DocumentType dType = new DocumentType(Int32.Parse(row["DocumentTypeId"].ToString()), row["DocumentTypeName"].ToString());
                documentTypes.Add(dType);
            }

            return documentTypes;
        }

        public DocumentType GetDocumentType(int documentTypeId)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand("daycare.GetDocumentType", con);
                    sda.SelectCommand.Parameters.AddWithValue("@DocumentTypeId", documentTypeId);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                DocumentType dType = new DocumentType(Int32.Parse(tempTable.Rows[0]["DocumentTypeId"].ToString()), tempTable.Rows[0]["DocumentTypeName"].ToString());

                return dType;
            }
            else
            {
                return null;
            }
        }

        public Document AddDocument(String documentName, String fileName, int documentTypeId, String expirationDate = "")
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.AddDocument", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentName", documentName);
                        comm.Parameters.AddWithValue("@FileName", fileName);
                        comm.Parameters.AddWithValue("@DocumentTypeId", documentTypeId);

                        if (expirationDate != "")
                        {
                            comm.Parameters.AddWithValue("@ExpirationDate", expirationDate);
                        }

                        con.Open();

                        int documentId = 0;
                        Int32.TryParse(comm.ExecuteScalar().ToString(), out documentId);

                        if (documentId > 0)
                        {
                            Document doc = new Document(documentId, GetDocumentType(documentTypeId), fileName, documentName);
                            if (expirationDate != "")
                            {
                                doc.expirationDate = expirationDate;
                            }

                            return doc;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch(SqlException)
            {
                return null;
            }
        }

        public void UpdateDocument(Document doc)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.UpdateDocument", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentId", doc.documentId);
                        comm.Parameters.AddWithValue("@DocumentName", doc.documentName);
                        comm.Parameters.AddWithValue("@DocumentTypeId", doc.dType.documentTypeId);

                        if (doc.expirationDate != "")
                        {
                            comm.Parameters.AddWithValue("@ExpirationDate", doc.expirationDate);
                        }

                        con.Open();
                        comm.ExecuteNonQuery();
                    }
                }
            }
            catch(SqlException) {}
        }

        public bool UpdateDocumentType(DocumentType docType)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.UpdateDocumentType", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentTypeId", docType.documentTypeId);
                        comm.Parameters.AddWithValue("@DocumentTypeName", docType.documentTypeName);

                        con.Open();
                        comm.ExecuteNonQuery();
                        
                        return true;
                    }
                }
            }
            catch (SqlException) 
            {
                return false;
            }
        }

        public bool AddDocumentType(String name)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.AddDocumentType", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentTypeName", name);

                        con.Open();
                        comm.ExecuteNonQuery();

                        return true;
                    }
                }
            }
            catch (SqlException)
            {
                return false;
            }
        }

        public bool DeleteDocumentType(DocumentType docType)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.DeleteDocumentType", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentTypeId", docType.documentTypeId);

                        con.Open();
                        comm.ExecuteNonQuery();

                        return true;
                    }
                }
            }
            catch (SqlException) 
            { 
                return false; 
            }
        }

        public void DeleteDocument(int documentId)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.DeleteDocument", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentId", documentId);

                        con.Open();
                        comm.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException) { }
        }

        public void AddEmployeeDocument(Document doc, Employee emp)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    using (SqlCommand comm = new SqlCommand("daycare.AddEmployeeDocument", con))
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("@DocumentId", doc.documentId);
                        comm.Parameters.AddWithValue("@EmployeeId", emp.employeeId);

                        con.Open();

                        comm.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException) { }
        }

        public List<Document> GetEmployeeDocuments(Employee employee, bool includeExpiredDocuments, DocumentType dType = null)
        {
            DataTable tempTable = new DataTable();
            List<Document> docs = new List<Document>();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand("daycare.GetEmployeeDocuments", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@EmployeeId", employee.employeeId);
                    sda.SelectCommand.Parameters.AddWithValue("@IncludeExpiredDocuments", includeExpiredDocuments);

                    if (dType != null)
                    {
                        sda.SelectCommand.Parameters.AddWithValue("@DocumentTypeId", dType.documentTypeId);
                    }

                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return docs;
            }

            foreach (DataRow row in tempTable.Rows)
            {
                Document doc = new Document(Int32.Parse(row["DocumentId"].ToString()), GetDocumentType(Int32.Parse(row["DocumentTypeId"].ToString())), row["FileName"].ToString(), row["DocumentName"].ToString());
                docs.Add(doc);
            }

            return docs;
        }

        public Document GetDocument(int documentId)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = new SqlCommand("daycare.GetDocument", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@DocumentId", documentId);

                    sda.Fill(tempTable);
                }
            }
            catch(SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                Document doc = new Document(Int32.Parse(tempTable.Rows[0]["DocumentId"].ToString()), GetDocumentType(Int32.Parse(tempTable.Rows[0]["DocumentTypeId"].ToString())), tempTable.Rows[0]["FileName"].ToString(), tempTable.Rows[0]["DocumentName"].ToString());
                
                if(tempTable.Rows[0]["ExpirationDate"] != null)
                {
                    DateTime dt = DateTime.Parse(tempTable.Rows[0]["ExpirationDate"].ToString());
                    
                    doc.expirationDate = dt.ToShortDateString();
                }

                return doc;
            }
            else
            {
                return null;
            }
        }
    }
}